package Ms

import (
	"encoding/json"
	"errors"
	"fmt"
	"db2s/ETL"
	ea "db2s/encryptionAlgorithm"
	"db2s/global"
	"reflect"
	"sort"
	"strconv"
	"strings"
)

type TableColumn struct {
}

// GetColumnType 数据类型判断，是字符串还是数字
func (ms TableColumn) GetColumnType(s global.GetColumnTypeInput) (result any, err error) {
	var (
		Type, null []string
	)
	for _, v := range s.ColumnFilter {
		if strings.EqualFold(v, "rowId") {
			return []string{"rowId"}, nil
		}
		for _, v1 := range s.TableColumn {
			if strings.EqualFold(v1.ColumnName, v) {
				switch strings.ToUpper(func(s1 string) (s string) {
					if n := strings.Index(s1, "("); n != -1 {
						s = s1[:n]
					} else {
						s = s1
					}
					return
				}(v1.DataType)) {
				case "INT", "NUMBER", "FLOAT", "INTEGER", "SMALLINT", "REAL", "BINARY_DOUBLE", "BINARY_FLOAT", "BINARY_INTEGER", "PLS_INTEGER", "NATURAL", "NATURALN", "POSITIVE", "POSITIVEN", "SIGNTYPE", "SIMPLE_INTEGER", "DECIMAL", "DEC", "NUMERIC":
					Type = append(Type, "int")
				case "VARCHAR2", "CHAR", "NCHAR", "ROWID", "VARCHAR", "NVARCHAR2":
					Type = append(Type, "string")
				case "SYSDATE", "DATE", "TIMESTAMP", "TIMESTAMP WITH TIME ZONE", "TIMESTAMP WITH LOCAL TIME ZONE":
					Type = append(Type, "datetime")
				default:
					Type = append(Type, "string")
				}
				null = append(null, v1.IsNull)
			}
		}
	}
	return global.GetIndexColumnProperty{
		NullGather: null,
		TypeGather: Type,
	}, nil
}
func (ms TableColumn) intTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct        global.ConvertColumnMeta
		unsigned  bool
		precision int
		err       error
	)
	if precision, err = ms.stringToInt(m.DataPrecision); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.FixedSwitch = true
	ct.DataLengthUnit = "byte"
	ct.GroupType = fmt.Sprintf("%v", m.DataType)
	switch strings.ToUpper(m.DataType) {
	case "BIT":
		/*
			存储u空间：BIT(M) 可以存储 M 位的二进制数据, 其中 M 的取值范围是 1 到 64
			默认值：如果不指定位数 M, 默认为 BIT(1)。
			赋值和检索:
				以二进制字符串的形式赋值: b'01010101'
				以十进制数字赋值: 65(等同于二进制 b'1000001')
				检索时以二进制字符串形式返回
			比较和运算:
				BIT 类型支持位运算, 如 &、|、^、~、<<、>>
				可以用 =、<>、<、>等比较运算符进行比较
			索引：BIT 类型的列可以创建索引, 但仅支持相等比较条件的查询
		*/
		ct.Type = "bit"
		ct.Format = "b01"
		if precision == 0 {
			precision = 1
		}
		ct.ShowSize = precision
		ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, precision)
		ct.MaxSize = 1
	case "TINYINT":
		/*
			TINYINT(1 byte) : -128 to 127 或 0 to 255 无符号
		*/
		unsigned = true
		ct.Symbol = true
		ct.Type = "int"
		ct.ShowSize = precision
		ct.Format = ">=-128 and <= 127"
		if unsigned {
			ct.Format = ">=0 and <= 255"
		}
		ct.MaxSize = precision
		ct.DataLength = 1
	case "SMALLINT":
		unsigned = false
		//SMALLINT(2 bytes) : -32768 to 32767 或 0 to 65535 无符号
		ct.Type = "int"
		ct.ShowSize = precision
		ct.Format = ">=-32768 and <= 32767"
		if unsigned {
			ct.Format = ">=0 and <= 65535"
		}
		ct.MaxSize = precision
		ct.DataLength = 2
	case "INT":
		unsigned = false
		//INT(4 bytes) : -2147483648 to 2147483647 或 0 to 4294967295 无符号
		ct.Type = "int"
		ct.Format = ">=-2147483648 and <= 2147483647"
		ct.MaxSize = precision
		ct.ShowSize = precision
		if unsigned {
			ct.Format = ">=0 and <= 4294967295"
		}
		ct.DataLength = 4
	case "BIGINT":
		unsigned = false
		//BIGINT(8 bytes) : -9223372036854775808 to 9223372036854775807 或 0 to 18446744073709551615 无符号
		ct.Type = "int"
		ct.ShowSize = precision
		ct.MaxSize = precision
		ct.Format = ">=-9223372036854775808 and <= 9223372036854775807"
		if unsigned {
			ct.Format = ">=0 and <= 18446744073709551615"
		}
		ct.DataLength = 8
	}
	return ct
}
func (ms TableColumn) FloatTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct               global.ConvertColumnMeta
		unsigned         bool
		precision, scale int
		err              error
	)

	if strings.Contains(strings.ToLower(m.DataType), "unsigned") {
		unsigned = true
	}
	if precision, err = ms.stringToInt(m.DataPrecision); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.DataLengthUnit = "byte"
	ct.Symbol = unsigned
	ct.FixedSwitch = true
	ct.GroupType = m.DataType
	switch strings.ToUpper(m.DataType) {
	case "FLOAT":
		/*
			存储空间:
				FLOAT 类型占用 8 个字节的存储空间。
				它可以表示的数值范围为 -1.79E+308 到 1.79E+308。
			精度:
				float 数据类型提供 53 位的精度，这意味着它可以存储大约 15-16 位有效数字。
			float 数据类型的表示
				科学计数法： float 数据类型使用科学计数法来表示数字，例如：123.45 可以表示为 1.2345 x 10^2。
				内部结构： float 数据类型内部由三个部分组成：
				符号位： 表示数字是正数还是负数。
				指数位： 表示 10 的幂次方。
				尾数位： 表示小数点后的数字。
			float 数据类型的局限性
				精度限制： 由于 float 数据类型只能存储大约 15-16 位有效数字，因此对于需要高精度计算的应用，它可能不适合。
				舍入误差： 由于 float 数据类型存储的是近似数值，因此在进行计算时可能会出现舍入误差。
		*/
		switch {
		case m.DataScale == "0":
			ct.ShowSize = precision
			ct.Format = "(precision)"
		case m.DataScale == "":
			ct.ShowSize = precision
			ct.Format = "(precision)"
		case len(m.DataScale) > 0:
			ct.ShowSize = fmt.Sprintf("%v,%v", precision, scale)
			ct.Format = "(precision,scale)"
		}
		ct.MaxSize = 53
		ct.Type = "double"
		ct.DataLength = 8

	case "REAL":
		/*
			1. real 数据类型的基本概念
				近似数值类型： real 是 SQL Server 中用于存储近似数值的类型，这意味着它无法精确地表示所有实数。
				IEEE 754 标准： SQL Server 使用 IEEE 754 标准来表示浮点数，该标准定义了浮点数的存储方式和计算规则。
			2. real 数据类型的精度和范围
				精度： real 数据类型提供 24 位的精度，这意味着它可以存储大约 7-8 位有效数字。
				范围： real 数据类型可以存储非常大的数字，范围从 -3.40E+38 到 3.40E+38。
			3. real 数据类型的存储大小
				real 数据类型占用 4 个字节 的存储空间。
			4. real 数据类型的表示
				科学计数法： real 数据类型使用科学计数法来表示数字，例如：123.45 可以表示为 1.2345 x 10^2。
				内部结构： real 数据类型内部由三个部分组成：
				符号位： 表示数字是正数还是负数。
				指数位： 表示 10 的幂次方。
				尾数位： 表示小数点后的数字。
			5. real 数据类型的局限性
				精度限制： 由于 real 数据类型只能存储大约 7-8 位有效数字，因此对于需要高精度计算的应用，它可能不适合。
				舍入误差： 由于 real 数据类型存储的是近似数值，因此在进行计算时可能会出现舍入误差
		*/
		switch {
		case m.DataScale == "0":
			ct.ShowSize = precision
			ct.Format = "(precision)"
		case m.DataScale == "":
			ct.ShowSize = precision
			ct.Format = "(precision)"
		case len(m.DataScale) > 0:
			if scale, err = ms.stringToInt(m.DataScale); err != nil {
				return global.ConvertColumnMeta{}
			}
			ct.ShowSize = fmt.Sprintf("%v,%v", precision, scale)
			ct.Format = "(precision,scale)"
		}
		ct.MaxSize = 24
		ct.Type = "float"
		ct.DataLength = 4
	}
	return ct
}
func (ms TableColumn) DecimalTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct               global.ConvertColumnMeta
		unsigned         bool
		precision, scale int
		err              error
	)
	if strings.Contains(strings.ToLower(m.DataType), "unsigned") {
		unsigned = true
	}
	if precision, err = ms.stringToInt(m.DataPrecision); err != nil {
		return global.ConvertColumnMeta{}
	}
	if scale, err = ms.stringToInt(m.DataScale); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.Type = "decimal"
	ct.FixedSwitch = false
	ct.DataLengthUnit = "byte"
	ct.Symbol = unsigned
	switch strings.ToUpper(m.DataType) {
	case "DECIMAL", "NUMERIC":
		/*
			DECIMAL 是 SQLServer 中用于存储精确小数的数据类型。与 FLOAT 和 DOUBLE 不同, DECIMAL 类型可以精确地表示小数,不会出现精度损失的问题。
			以下是 DECIMAL 类型的一些特点:
				声明方式:
					DECIMAL(P,Ss) 其中 P 表示数字的总位数(包括小数部分),Ss 表示小数部分的位数。
					例如 DECIMAL(10,2) 表示一个总共 10 位,小数部分 2 位的小数。
				存储空间:
					DECIMAL 类型的存储空间是可变的,取决于声明时的 M 和 D 值。
					每个 DECIMAL(M,D) 值占用 M+2 个字节的存储空间(整数部分最多可以有 M-D 位,每个整数位需要 1 个字节来存储,小数部分最多可以有 D 位,每个小数位需要 1 个字节来存储,为了表示正负数,需要额外使用 1 个字节来存储符号位)。
				精度:
					DECIMAL 类型可以精确地表示小数,没有像 FLOAT 和 DOUBLE 那样的精度损失问题。
					最大精度为 65 位数字,小数部分最多 30 位。
				取值范围:
					DECIMAL(P,Ss) 的取值范围为 -10^P +1 to 10^P -1。
					例如 DECIMAL(10,2) 的取值范围为 -99999999.99 to 99999999.99。
				舍入规则:
					如果插入的数值超出了 DECIMAL 的存储范围,SQL SERVER 会按照四舍五入的方式进行舍入。
		*/
		switch {
		case m.DataScale == "0":
			ct.ShowSize = precision
			ct.Format = "(precision)"
			ct.GroupType = m.DataType
			ct.GroupType = fmt.Sprintf("%v(%v,0)", m.DataType, precision)
		case len(m.DataScale) > 0:
			ct.ShowSize = fmt.Sprintf("%v,%v", precision, scale)
			ct.Format = "(precision,scale)"
			ct.GroupType = fmt.Sprintf("%v(%v,%v)", m.DataType, precision, scale)
		}
		switch {
		case precision < 10:
			ct.DataLength = 5
		case precision < 20:
			ct.DataLength = 9
		case precision < 29:
			ct.DataLength = 13
		case precision < 39:
			ct.DataLength = 17
		}
		ct.MaxSize = 38
	case "SMALLMONEY":
		switch {
		case m.DataScale == "0":
			ct.ShowSize = precision
			ct.Format = "(precision)"
			ct.GroupType = fmt.Sprintf("%v(%v,0)", m.DataType, precision)
		case len(m.DataScale) > 0:
			ct.ShowSize = fmt.Sprintf("%v,%v", precision, scale)
			ct.Format = "(precision,scale)"
			ct.GroupType = fmt.Sprintf("%v(%v,%v)", m.DataType, precision, scale)
		}
		ct.DataLength = 4
	case "MONEY":
		switch {
		case m.DataScale == "0":
			ct.ShowSize = precision
			ct.Format = "(precision)"
			ct.GroupType = fmt.Sprintf("%v(%v,0)", m.DataType, precision)
		case len(m.DataScale) > 0:
			ct.ShowSize = fmt.Sprintf("%v,%v", precision, scale)
			ct.Format = "(precision,scale)"
			ct.GroupType = fmt.Sprintf("%v(%v,%v)", m.DataType, precision, scale)
		}
		ct.DataLength = 4
	}
	return ct
}
func (ms TableColumn) timeTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct       global.ConvertColumnMeta
		unsigned bool
		scale    int
		err      error
	)
	//https://learn.microsoft.com/zh-cn/sql/t-sql/data-types/date-transact-sql?view=sql-server-ver16
	if scale, err = ms.stringToInt(m.DataScale); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.Type = "time"
	ct.FixedSwitch = true
	ct.GroupType = m.DataType
	ct.ShowSize = scale
	ct.DataLengthUnit = "byte"
	ct.Symbol = unsigned
	switch strings.ToUpper(m.DataType) {
	case "DATE":
		/*
			DATE 类型:
				存储日期,格式为 YYYY-MM-DD。
				取值范围从 1000-01-01 到 9999-12-31。
				占用 3 个字节的存储空间。
		*/
		ct.DataLength = 3
		ct.Format = "year-month-day"
	case "TIME":
		/*
			TIME 类型:
					存储时间,格式为 HH:MM:SS。
					取值范围从 -838:59:59 到 838:59:59。
					占用 3 个字节的存储空间。
				MySQL 中 TIME(n) 数据类型的 n 最大值是 6。也就是说,MySQL 支持的最大时间值小数精度为 6 位小数。
			具体解释如下:
				TIME(n) 数据类型用于存储时间值,其中 n 表示小数部分的位数。
					当 n 取值为 0 时,时间值精确到秒。
					当 n 取值为 1 到 7 时,时间值精确到小数点后 1 到 7 位。
					n 的最大值为 6，这意味着 TIME(7) 可以存储时间值精确到微秒(百万分之一秒)。
			例如:
					TIME(0) 存储为 'HH:MM:SS'
					TIME(3) 存储为 'HH:MM:SS.sss'
					TIME(6) 存储为 'HH:MM:SS.sssSss'
					需要注意的是,虽然 MySQL 支持 TIME(n) 最大 n 值为 7，但实际使用时根据需要选择合适的精度即可。过高的时间值精度可能会占用更多的存储空间,并且在某些情况下可能无法提供额外的价值。
		*/
		ct.DataLength = 3
		switch scale {
		case 0:
			ct.Format = "hour:minute:second"
		case 1:
			ct.Format = "hour:minute:second.milli(1)"
		case 2:
			ct.Format = "hour:minute:second.milli(2)"
		case 3:
			ct.Format = "hour:minute:second.milli(3)"
		case 4:
			ct.Format = "hour:minute:second.micro(1)"
		case 5:
			ct.Format = "hour:minute:second.micro(2)"
		case 6:
			ct.Format = "hour:minute:second.micro(3)"
		case 7:
			ct.Format = "hour:minute:second.nano(1)"
		}
		ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, scale)
		ct.MaxSize = 6
	case "SMALLDATETIME":
		ct.Format = "year-month-day hour:minute:second"
		ct.MaxSize = 0
		ct.DataLength = 4
	case "DATETIME":
		/*
			DATETIME 类型:
				存储日期和时间,格式为 YYYY-MM-DD HH:MM:SS。
				取值范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59。
				占用 8 个字节的存储空间。
		*/
		ct.Format = "year-month-day hour:minute:second.milli(3)"
		ct.MaxSize = 3
		ct.DataLength = 8
	case "DATETIME2":
		/*
			DATETIME 类型:
				存储日期和时间,格式为 YYYY-MM-DD HH:MM:SS。
				取值范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59。
				占用 8 个字节的存储空间。
		*/
		switch scale {
		case 0:
			ct.Format = "year-month-day hour:minute:second"
			ct.DataLength = 6
		case 1:
			ct.Format = "year-month-day hour:minute:second.milli(1)"
			ct.DataLength = 6
		case 2:
			ct.Format = "year-month-day hour:minute:second.milli(2)"
			ct.DataLength = 6
		case 3:
			ct.Format = "year-month-day hour:minute:second.milli(3)"
			ct.DataLength = 7
		case 4:
			ct.Format = "year-month-day hour:minute:second.micro(1)"
			ct.DataLength = 7
		case 5:
			ct.Format = "year-month-day hour:minute:second.micro(2)"
			ct.DataLength = 8
		case 6:
			ct.Format = "year-month-day hour:minute:second.micro(3)"
			ct.DataLength = 8
		case 7:
			ct.Format = "year-month-day hour:minute:second.nano(1)"
			ct.DataLength = 8
		}
		ct.MaxSize = 7
		ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, scale)
	case "DATETIMEOFFSET":
		/*
			MySQL 中的 TIMESTAMP 数据类型是一种特殊的日期时间类型,具有以下特点:
			定义:
				TIMESTAMP 用于存储日期和时间信息,包括年、月、日、时、分、秒。时间戳的格式为 YYYY-MM-DD HH:MM:SS。
			范围:
				TIMESTAMP 的取值范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。
			存储大小:
				TIMESTAMP 类型占用 4 个字节的存储空间。
			时区支持:
				TIMESTAMP 值是以 UTC 时区存储的。在插入或查询时,MySQL 会根据当前会话的时区自动进行转换。
			默认值:
				如果不指定 TIMESTAMP 列的默认值,则默认为当前时间戳。可以通过 DEFAULT CURRENT_TIMESTAMP 指定默认值为当前时间戳。
			自动更新:
				TIMESTAMP 列可以被定义为在行更新时自动更新为当前时间戳。通过 ON UPDATE CURRENT_TIMESTAMP 可以实现自动更新。
			与 DATETIME 的区别:
				DATETIME 是一种独立的日期时间类型,不受时区影响。TIMESTAMP 受时区影响,存储的是 UTC 时间,查询时会根据会话时区转换。
		*/
		ct.Type = "timestamp"
		switch scale {
		case 0:
			ct.Type = "time"
			ct.Format = "year-month-day hour:minute:second"
			ct.DataLength = 8
		case 1:
			ct.Format = "year-month-day hour:minute:second.milli(1).utc"
			ct.DataLength = 8
		case 2:
			ct.Format = "year-month-day hour:minute:second.milli(2).utc"
			ct.DataLength = 8
		case 3:
			ct.Format = "year-month-day hour:minute:second.milli(3).utc"
			ct.DataLength = 9
		case 4:
			ct.Format = "year-month-day hour:minute:second.micro(1).utc"
			ct.DataLength = 9
		case 5:
			ct.Format = "year-month-day hour:minute:second.micro(2).utc"
			ct.DataLength = 10
		case 6:
			ct.Format = "year-month-day hour:minute:second.micro(3).utc"
			ct.DataLength = 10
		case 7:
			ct.Format = "year-month-day hour:minute:second.nano(1).utc"
			ct.DataLength = 10
		}
		ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, scale)
	}
	return ct
}
func (ms TableColumn) charTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct                     global.ConvertColumnMeta
		charLength, dataLength int
		err                    error
	)
	/*
		定义长度:
			CHAR 类型需要在定义时指定长度,最大长度为 255 个字符。
			例如, CHAR(10) 表示最多可以存储 10 个字符的字符串。
		存储空间:
			CHAR 类型会占用固定的存储空间,即定义的长度个字节。
			例如, CHAR(10) 类型会占用 10 个字节的存储空间。
		填充空格:
			如果存储的字符串长度小于定义的长度,CHAR 类型会用空格进行填充。
			例如, 存储 "hello" 到 CHAR(10) 类型,实际存储的值为 "hello "。
		比较和检索:
			在比较或检索 CHAR 类型时,会自动删除尾部的空格。所以 "hello " 和 "hello" 在比较时会被认为是相等的。
		性能:
			由于 CHAR 类型占用固定的存储空间,因此在检索和比较时性能较好。
			但如果大多数情况下存储的字符串长度小于定义的长度,会浪费存储空间。
		使用场景:
			CHAR 类型适合用于存储固定长度的数据,例如国家代码、邮政编码等。
			如果字符串长度不确定,建议使用 VARCHAR 类型。
	*/
	if charLength, err = ms.stringToInt(m.CharLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	if dataLength, err = ms.stringToInt(m.DataLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	ct.Type = "char"
	ct.FixedSwitch = true
	ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, m.CharLength)
	if !strings.HasPrefix(m.DataType, "N") {
		ct.GroupType = fmt.Sprintf("char(%v)", m.CharLength)
		if dataLength > charLength { //处理char(10 char)
			ct.DataLengthUnit = "char"
		} else {
			ct.DataLengthUnit = "byte"
		}
	} else {
		ct.DataLengthUnit = "char"
	}
	ct.ShowSize = charLength
	ct.DataLength = dataLength
	return ct
}
func (ms TableColumn) varcharTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct                     global.ConvertColumnMeta
		charLength, dataLength int
		err                    error
	)
	/*
		定义长度:
			VARCHAR 类型需要在定义时指定最大长度,最大长度为 65,535 个字符。
			例如, VARCHAR(50) 表示最多可以存储 50 个字符的字符串。
		存储空间:
			VARCHAR 类型会根据实际存储的字符串长度来占用存储空间。存储空间为字符串长度加 1 或 2 个字节,具体取决于最大长度是否超过 255 个字符。
			例如, 存储 "hello" 到 VARCHAR(50) 类型,实际占用 6 个字节的存储空间。
		填充空格:
			与 CHAR 类型不同, VARCHAR 类型不会自动填充空格。存储的字符串长度有多长,就占用多少存储空间。
		比较和检索:
			在比较或检索 VARCHAR 类型时,会保留字符串中的空格。所以 "hello" 和 "hello " 在比较时会被认为是不相等的。
		性能:
			由于 VARCHAR 类型的存储空间是可变的,因此在某些情况下性能可能略低于 CHAR 类型。但相比之下, VARCHAR 类型更加灵活,能够更好地适应不确定长度的字符串。
		使用场景:
			VARCHAR 类型适合用于存储长度不确定的字符串数据,例如用户名、地址、备注等。如果字符串长度基本固定,建议使用 CHAR 类型以获得更好的性能。
	*/
	if charLength, err = ms.stringToInt(m.CharLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	if dataLength, err = ms.stringToInt(m.DataLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	if charLength == -1 {
		ct.Type = "text"
		ct.GroupType = fmt.Sprintf("varchar(max)")
		ct.ShowSize = 715827882
		ct.DataLength = 2147483648
		return ct
	}
	ct.Type = "varchar"
	ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, charLength)
	if !strings.HasPrefix(strings.ToUpper(m.DataType), "N") {
		ct.GroupType = fmt.Sprintf("varchar(%v)", charLength)
		if dataLength > charLength {
			ct.DataLengthUnit = "char"
		} else {
			ct.DataLengthUnit = "byte"
		}
	} else {
		ct.DataLengthUnit = "char"
	}
	ct.ShowSize = charLength
	ct.DataLength = dataLength

	return ct
}
func (ms TableColumn) textTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct global.ConvertColumnMeta
	)
	ct.Type = "text"
	ct.DataLengthUnit = "byte"
	ct.GroupType = m.DataType
	switch strings.ToUpper(m.DataType) {
	case "TEXT", "XML":
		ct.ShowSize = 715827882
		ct.DataLength = 2147483648
	case "NTEXT":
		ct.ShowSize = 357913941
		ct.DataLength = 1073741823
	}
	return ct
}
func (ms TableColumn) lobTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct                     global.ConvertColumnMeta
		charLength, dataLength int
		err                    error
	)
	if charLength, err = ms.stringToInt(m.CharLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	if dataLength, err = ms.stringToInt(m.DataLength); err != nil {
		return global.ConvertColumnMeta{}
	}
	switch strings.ToUpper(m.DataType) {
	case "BINARY":
		ct.Type = "lob"
		ct.DataLengthUnit = "byte"
		ct.FixedSwitch = true
		ct.ShowSize = charLength
		ct.DataLength = dataLength
		ct.MaxSize = 8000
		ct.GroupType = m.DataType
		if charLength > 1 {
			ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, charLength)
		}
	case "UNIQUEIDENTIFIER":
		ct.Type = "lob"
		ct.DataLengthUnit = "byte"
		ct.FixedSwitch = true
		ct.ShowSize = dataLength
		ct.DataLength = dataLength
		ct.GroupType = m.DataType
		if charLength > 1 {
			ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, dataLength)
		}
	case "VARBINARY":
		ct.Type = "lob"
		ct.DataLengthUnit = "byte"
		ct.ShowSize = charLength
		if charLength == -1 {
			ct.ShowSize = 8000
			charLength = 8000
		}
		ct.DataLength = dataLength
		ct.MaxSize = 8000
		ct.GroupType = m.DataType
		if charLength > 1 {
			ct.GroupType = fmt.Sprintf("%v(%v)", m.DataType, charLength)
		}
	case "IMAGE":
		ct.Type = "lob"
		ct.DataLengthUnit = "byte"
		ct.DataLength = 2147483647
		ct.ShowSize = 2147483647
		ct.MaxSize = 2147483647
		ct.GroupType = m.DataType
	}
	return ct
}
func (ms TableColumn) siteTypeRedefine(m global.TableMeta) global.ConvertColumnMeta {
	var (
		ct global.ConvertColumnMeta
	)
	switch strings.ToUpper(m.DataType) {
	case "GEOGRAPHY": //三维空间   主要单位是度，用于立体
		ct.Type = "site"
		ct.GroupType = m.DataType
		ct.Format = fmt.Sprintf("degree")
	case "GEOMETRY": //二维空间  只要单位是米，用于平面
		ct.Type = "site"
		ct.GroupType = m.DataType
		ct.Format = fmt.Sprintf("meter")
	}
	return ct
}
func (ms TableColumn) stringToInt(s string) (int, error) {
	if len(s) == 0 {
		return 0, nil
	}
	if l, err := strconv.Atoi(s); err != nil {
		return 0, err
	} else {
		return l, nil
	}
}

// DefaultValueQuotation 处理默认值是否+引号问题，int类型或使用函数则不需要引号
func (ms TableColumn) DefaultValueQuotation(m global.TableMeta) bool {
	for _, v := range []string{
		"TINYINT", "SMALLINT", "INT", "BIGINT", "DECIMAL", "NUMERIC", "MONEY", "SMALLMONEY", "BIT",
	} {
		if strings.HasPrefix(strings.ToUpper(m.DataType), v) {
			return true
		}
	}
	return false
}
func (ms TableColumn) DefaultValueFunc(m global.TableMeta) string {
	switch {
	case strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "CURRENT_TIMESTAMP"):
		return "year-month-day hour:minute:second.micro(3).utc"
	case strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "CURRENT_DATE"), strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "NOW()"):
		return "year-month-day hour:minute:second"
	case strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "CURRENT_TIME"):
		return "hour:minute:second"
	case strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "USER()"):
		return "current_user@host"
	case strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "CURRENT_USER()"):
		return "current_user"
	case strings.HasSuffix(strings.ToUpper(m.ColumnDefault), "SYSTEM_USER()"):
		return "current_sys_user"
	case strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "UUID()"):
		return "uuid"
	case strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "RAND()"):
		return "randValue"
	case strings.HasPrefix(strings.ToUpper(m.ColumnDefault), "CONNECTION_ID()"):
		return "current_id"
	default:
		return ""
	}
}
func (ms TableColumn) DefaultValue(m global.TableMeta) global.ConvertDefaultValue {
	var (
		ct global.ConvertDefaultValue
	)
	defaultValue := m.ColumnDefault
	ct.OldValue = defaultValue
	//处理函数原始数据
	defaultValue = strings.ReplaceAll(defaultValue, "\\", "")
	if strings.HasPrefix(defaultValue, "((") {
		l := strings.ReplaceAll(defaultValue, "((", "")
		l = strings.ReplaceAll(l, "))", "")
		defaultValue = fmt.Sprintf("%v", l)
	} else {
		if strings.HasPrefix(defaultValue, "(") && strings.HasSuffix(defaultValue, ")") {
			l := defaultValue[1 : len(defaultValue)-1]
			defaultValue = fmt.Sprintf("%v", l)
		}
	}
	//处理default为空或空字符串
	if strings.EqualFold(defaultValue, "''") || len(defaultValue) == 0 {
		if ms.DefaultValueQuotation(m) {
			if strings.EqualFold(m.IsNull, "NO") {
				ct.NewValue = ""
			}
		} else {
			if strings.EqualFold(m.IsNull, "NO") {
				ct.NewValue = "''"
			}
		}
		ct.NewValue = "NULL"
		return ct
	}
	//判断default 值是否包含N''
	if strings.HasPrefix(defaultValue, "N'") && strings.HasSuffix(defaultValue, "'") {
		defaultValue = defaultValue[2 : len(defaultValue)-1]
	}
	//判断default 是否用到函数
	ct.Format = ms.DefaultValueFunc(m)
	ct.NewValue = defaultValue
	return ct
}

// ColumnTypeRedefine 根据相应规范，重新定义数据类型
func (ms TableColumn) ColumnTypeRedefine(m global.TableMeta) global.TableMeta {
	var (
		event = "[ColumnTypeRedefine]"
		ccm   global.ConvertColumnMeta
		cdv   global.ConvertDefaultValue
		n     = m
	)
	switch strings.ToUpper(m.DataType) {
	case "BIT", "TINYINT", "SMALLINT", "INT", "BIGINT": //整数数值类型
		ccm = ms.intTypeRedefine(m)
	case "FLOAT", "REAL": //单精度和双精度的浮点数类型
		ccm = ms.FloatTypeRedefine(m)
	case "DECIMAL", "NUMERIC", "MONEY", "SMALLMONEY": //定长数值类型
		ccm = ms.DecimalTypeRedefine(m)
	case "DATE", "TIME", "SMALLDATETIME", "DATETIME", "DATETIME2", "DATETIMEOFFSET": //时间类型
		ccm = ms.timeTypeRedefine(m)
	case "CHAR", "NCHAR": //字符串定长
		ccm = ms.charTypeRedefine(m)
	case "VARCHAR", "NVARCHAR": //字符串变长
		ccm = ms.varcharTypeRedefine(m)
	case "TEXT", "NTEXT", "XML": //大文本类型
		ccm = ms.textTypeRedefine(m)
	case "BINARY", "VARBINARY", "IMAGE", "UNIQUEIDENTIFIER": //二进制类型
		ccm = ms.lobTypeRedefine(m)
	case "GEOGRAPHY", "GEOMETRY":
		ccm = ms.siteTypeRedefine(m)
	default:
		WLog.Warn(fmt.Sprintf("(%v) %v The data type %v of column name %v of current table %v.%v does not match, the conversion cannot be performed, and the default value is returned.", 1, event, m.DataType, m.ColumnName, m.DatabaseName, m.TableName))
	}
	cdv = ms.DefaultValue(m)
	n.ConvertColumnType = ccm
	n.ColumnType = ccm.GroupType
	n.ConvertDefaultValue = cdv
	return n
}

/*
All server 获取校验表的列信息，包含列名，列序号，列类型
*/
func (ms TableColumn) All(s global.GetColumnTypeInput) (result global.AllReturnResult, err error) {
	var (
		event = "[sqlserverAll]"
		f1    any
	)
	result.BaseResult.Sql = fmt.Sprintf("SELECT \n"+
		"	isc.table_schema AS databaseName, \n"+
		"	isc.table_name AS tableName, \n"+
		"	isc.column_name AS columnName, \n"+
		"	isc.data_type as dataType, \n"+
		"	CAST(sc.precision AS VARCHAR ) as dataPrecision, \n"+
		"	CAST(sc.scale AS VARCHAR ) as dataScale, \n"+
		"	CAST(sc.max_length AS VARCHAR ) as dataLength, \n"+
		"	CAST(isc.character_maximum_length AS VARCHAR )  as charLength, \n"+
		"	CAST ( isc.ordinal_position AS VARCHAR ) AS columnSeq, \n"+
		"	isc.is_nullable AS isNull, \n"+
		"	isc.character_set_name AS charset, \n"+
		"	isc.COLLATION_NAME AS collationName, \n"+
		"	ep.VALUE AS columnComment, \n"+
		"	isc.COLUMN_DEFAULT AS columnDefault \n"+
		"FROM \n"+
		"	INFORMATION_SCHEMA.COLUMNS isc \n"+
		"LEFT JOIN sys.columns sc \n"+
		"ON \n"+
		"	isc.column_name = sc.name AND sc.object_id = OBJECT_ID( isc.table_name ) \n"+
		"LEFT JOIN sys.extended_properties ep \n"+
		"ON \n"+
		"	ep.minor_id = sc.column_id AND ep.major_id = sc.object_id \n"+
		"where \n"+
		"	isc.table_schema in ('%s') and isc.TABLE_NAME in ('%s') \n"+
		"ORDER BY "+
		"	ordinal_position", s.TableInfo.Schema, s.TableInfo.Table)
	active := ETL.InstanceActiveS{DBType: DBType, DB: s.DB["single"], SqlMode: true}
	if len(s.TableInfo.Schema) == 0 || len(s.TableInfo.Table) == 0 || len(s.DB) == 0 || active.DB == nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{schema:%v,table:%v,sql:%v,shard:%v,db:%v,err:%v}", event, s.TableInfo.Schema, s.TableInfo.Table, result.BaseResult.Sql, "single", s.DB, err))
		return
	}
	if result.BaseResult.TimeOut, f1, err = active.SqlQuery(result.BaseResult.Sql, ETL.SMap); err != nil {
		err = errors.New(fmt.Sprintf("%v sql exec fail. Execution process:{sql:%v,err:%v}", event, result.BaseResult.Sql, err))
		return
	}
	if f1 == nil || len(f1.([]map[string]any)) == 0 {
		err = errors.New(fmt.Sprintf("%v Get the column data of table %v.%v is empty. Execution process{exec sql:%v result:%v}", event, s.TableInfo.Schema, s.TableInfo.Table, result.BaseResult.Sql, f1))
		return
	}
	for _, v := range f1.([]map[string]interface{}) {
		var m = global.TableMeta{}
		var jsonData []byte
		jsonData, err = json.Marshal(v)
		if err != nil {
			err = errors.New(fmt.Sprintf("%v []map[string]any strconv json fail. Execution process{strconv data:%v error:%v}", event, v, err))
			return
		}
		err = json.Unmarshal(jsonData, &m)
		if err != nil {
			err = errors.New(fmt.Sprintf("%v []byte json unmarshal struct fail. Execution process{strconv data:%v error:%v}", event, string(jsonData), err))
			return
		}
		n := ms.ColumnTypeRedefine(m)
		result.Result = append(result.Result, n)
	}
	return
}

func (ms TableColumn) DropColumnSql(s global.GetColumnTypeInput) (any, error) {
	return fmt.Sprintf("alter table `%s`.`%s` drop column `%s`;", s.TableInfo.Schema, s.TableInfo.Table, s.DdlColumn.Name), nil
}

/*
TypeConvert 目标端根据通用协议进行数据类型转换
*/
func (ms TableColumn) TypeConvert(s global.TableMeta, factor int) (cType string, cByte int) {
	ct := s.ConvertColumnType
	switch ct.Type {
	case "bit":
		if !strings.EqualFold(ct.Format, "b01") {
			return
		}
		cType = fmt.Sprintf("bit(%v)", ct.ShowSize)
	case "int":
		switch ct.DataLength {
		case 1:
			cType = fmt.Sprintf("TINYINT")
			if ct.Symbol {
				cType = fmt.Sprintf("TINYINT unsigned ")
			}
		case 2:
			cType = fmt.Sprintf("SMALLINT")
			if ct.Symbol {
				cType = fmt.Sprintf("SMALLINT unsigned ")
			}
		case 3:
			cType = fmt.Sprintf("MEDIUMINT")
			if ct.Symbol {
				cType = fmt.Sprintf("MEDIUMINT unsigned ")
			}
		case 4:
			cType = fmt.Sprintf("INT")
			if ct.Symbol {
				cType = fmt.Sprintf("INT unsigned ")
			}
		case 8:
			cType = fmt.Sprintf("BIGINT")
			if ct.Symbol {
				cType = fmt.Sprintf("BIGINT unsigned ")
			}
		}
		cByte = ct.DataLength
	case "decimal":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			cType = fmt.Sprintf("decimal(%v,0)", ct.ShowSize)
		case "string":
			cType = fmt.Sprintf("decimal(%v)", ct.ShowSize)
		}
		cByte = ct.DataLength
	case "float":
		var typeName string
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.MaxSize)) {
		case "int":
			typeName = "float"
			if ct.MaxSize.(int) > 23 && ct.MaxSize.(int) <= 54 {
				typeName = "double"
			}
		}
		cType = fmt.Sprintf("%v(%v)", typeName, ct.ShowSize)
		cByte = 4
	case "double":
		cType = fmt.Sprintf("double(%v)", ct.ShowSize)
		cByte = 8
	case "char":
		cType = fmt.Sprintf("char(%v)", ct.ShowSize)
		cByte = ct.ShowSize.(int) * factor
	case "varchar":
		cType = fmt.Sprintf("varchar(%v)", ct.ShowSize)
		cByte = ct.ShowSize.(int) * factor
		if cByte > 255 {
			cByte = cByte + 2
		} else {
			cByte = cByte + 1
		}
	case "time":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			switch ct.Format {
			case "year-month-day":
				cType = fmt.Sprintf("date")
				cByte = 3
			case "hour:minute:second":
				cType = fmt.Sprintf("time(0)")
				cByte = 3
			case "hour:minute:second.milli(1)", "hour:minute:second.milli(2)", "hour:minute:second.milli(3)":
				cType = fmt.Sprintf("time(%v)", ct.ShowSize)
				cByte = 3
			case "hour:minute:second.micro(1)", "hour:minute:second.micro(2)", "hour:minute:second.micro(3)":
				cType = fmt.Sprintf("time(%v)", ct.ShowSize)
				cByte = 3
			case "year":
				cType = fmt.Sprintf("year")
				cByte = 1
			case "year-month-day hour:minute:second":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
				cByte = 8
			case "year-month-day hour:minute:second.milli(1)", "year-month-day hour:minute:second.milli(2)", "year-month-day hour:minute:second.milli(3)":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
				cByte = 8
			case "year-month-day hour:minute:second.micro(1)", "year-month-day hour:minute:second.micro(2)", "year-month-day hour:minute:second.micro(3)":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
				cByte = 8
			}
		}
	case "timestamp":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			switch ct.Format {
			case "year-month-day hour:minute:second.utc":
				cType = fmt.Sprintf("timestamp(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.milli(1)", "year-month-day hour:minute:second.milli(2)", "year-month-day hour:minute:second.milli(3)":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.milli(1).utc", "year-month-day hour:minute:second.milli(2).utc", "year-month-day hour:minute:second.milli(3).utc":
				cType = fmt.Sprintf("timestamp(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.micro(1).utc", "year-month-day hour:minute:second.micro(2).utc", "year-month-day hour:minute:second.micro(3).utc":
				cType = fmt.Sprintf("timestamp(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.micro(1)", "year-month-day hour:minute:second.micro(2)", "year-month-day hour:minute:second.micro(3)":
				cType = fmt.Sprintf("datetime(%v)", ct.ShowSize)
			case "year-month-day hour:minute:second.nano(1)", "year-month-day hour:minute:second.nano(2)", "year-month-day hour:minute:second.nano(3)":
				cType = fmt.Sprintf("datetime(6)")
			case "year-month-day hour:minute:second.nano(1).utc", "year-month-day hour:minute:second.nano(2).utc", "year-month-day hour:minute:second.nano(3).utc":
				cType = fmt.Sprintf("timestamp(6)")
			}
		}
		cByte = 4
	case "text":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			switch {
			case ct.ShowSize.(int) <= 255:
				cType = fmt.Sprintf("TINYTEXT")
			case ct.ShowSize.(int) <= 65535:
				cType = fmt.Sprintf("TEXT")
			case ct.ShowSize.(int) <= 16777215:
				cType = fmt.Sprintf("MEDIUMTEXT")
			case ct.ShowSize.(int) <= 4294967295:
				cType = fmt.Sprintf("LONGTEXT")
			default:
				cType = fmt.Sprintf("LONGTEXT")
			}
		}
	case "lob":
		switch fmt.Sprintf("%v", reflect.TypeOf(ct.ShowSize)) {
		case "int":
			switch {
			case ct.ShowSize.(int) <= 255:
				cType = fmt.Sprintf("BINARY(%v)", ct.ShowSize)
			case ct.ShowSize.(int) > 255:
				cType = fmt.Sprintf("VARBINARY(%v)", ct.ShowSize)
			}
		}
	}
	return
}
func (ms TableColumn) AddColumnSql(s global.GetColumnTypeInput) (any, error) {
	var (
		TypeConstraint string
		nullConstraint = fmt.Sprintf(" NULL ")
		ChartSetValue  string
		CollationValue string
		DefaultValue   string
		CommentValue   string
		factor         = 1
	)
	TypeConstraint, _ = ms.TypeConvert(s.DdlColumn.SMeta, factor)
	if strings.EqualFold(s.DdlColumn.SMeta.IsNull, "NO") {
		nullConstraint = fmt.Sprintf(" NOT NULL ")
	}
	if len(s.DdlColumn.SMeta.ColumnDefault) > 0 || strings.EqualFold(s.DdlColumn.SMeta.ColumnDefault, "''") {
		DefaultValue = fmt.Sprintf("DEFAULT %v", s.DdlColumn.SMeta.ColumnDefault)
	}
	if len(s.DdlColumn.SMeta.ColumnComment) > 0 {
		CommentValue = fmt.Sprintf("comment %v", s.DdlColumn.SMeta.ColumnComment)
	} else {
		CommentValue = fmt.Sprintf("comment ''")
	}
	return fmt.Sprintf("alter table `%s`.`%s` add column `%s` %v %v %v %v %v %v;", s.TableInfo.Schema, s.TableInfo.Table, s.DdlColumn.Name, TypeConstraint, nullConstraint, ChartSetValue, CollationValue, DefaultValue, CommentValue), nil
}
func (ms TableColumn) DefaultValueConvert(l global.ConvertDefaultValue) string {
	switch {
	case len(l.Format) == 0:
		return l.NewValue
	case len(l.Format) > 0:
		switch l.Format {
		case "year-month-day hour:minute:second.micro(3).utc", "year-month-day hour:minute:second.micro(3)":
			return "CURRENT_TIMESTAMP"
		case "year-month-day hour:minute:second":
			return "CURRENT_TIMESTAMP"
		case "hour:minute:second":
			return "CURRENT_TIME"
		case "current_user@host":
			return "USER()"
		case "current_sys_user":
			return "SYSTEM_USER()"
		case "randValue":
			return "RAND()"
		case "uuid":
			return "UUID()"
		case "current_user":
			return "CURRENT_USER()"
		case "current_id":
			return "CONNECTION_ID()"
		}
	}
	return ""
}
func (ms TableColumn) ModifyColumnSql(s global.GetColumnTypeInput) (any, error) {
	var (
		TypeConstraint string
		nullConstraint = fmt.Sprintf(" NULL ")
		ChartSetValue  string
		CollationValue string
		DefaultValue   string
		CommentValue   string
		factor         = 1
		record         string
		alterSql       string
	)
	//处理因异构数据库导致的差异
	/*
		1：因为主键索引导致的null值和not null的差异
		2：因为null和not null的差异导致的default值的差异
	*/
	var abGather = make(map[string]int)
	if l, err := global.StructSubJsonNameToMap(*s.DdlColumn.Attributes); err != nil {
		return nil, err
	} else {
		for k, v := range l {
			if v.(bool) {
				abGather[k]++
			}
		}
	}
	if s.DdlColumn.Attributes.Type {
		TypeConstraint, _ = ms.TypeConvert(s.DdlColumn.SMeta, factor)
	} else {
		TypeConstraint = s.DdlColumn.DMeta.ColumnType
	}
	partTrue := strings.Contains(strings.ToLower(strings.Join(s.DdlColumn.PartColumn, ",")), strings.ToLower(s.DdlColumn.Name))
	priTrue := strings.Contains(strings.ToLower(strings.Join(s.DdlColumn.PriColumn, ",")), strings.ToLower(s.DdlColumn.Name))
	if strings.EqualFold(s.DdlColumn.SMeta.IsNull, "YES") && s.DdlColumn.Attributes.Null {
		if partTrue && priTrue {
			record = fmt.Sprintf("primary key include partition column must not null")
		} else if priTrue && !partTrue {
			record = fmt.Sprintf("primary key must not null")
		}
		nullConstraint = fmt.Sprintf(" NOT NULL ")
	} else if strings.EqualFold(s.DdlColumn.SMeta.IsNull, "NO") && s.DdlColumn.Attributes.Null {
		nullConstraint = fmt.Sprintf(" NOT NULL ")
	}
	if len(s.DdlColumn.SMeta.ColumnDefault) > 0 || strings.EqualFold(s.DdlColumn.SMeta.ColumnDefault, "''") {
		DefaultValue = fmt.Sprintf(" DEFAULT %v", ms.DefaultValueConvert(s.DdlColumn.SMeta.ConvertDefaultValue))
	} else {
		if strings.EqualFold(strings.TrimSpace(nullConstraint), "NOT NULL") {
			DefaultValue = fmt.Sprintf(" DEFAULT '' ")
		}
	}
	if len(s.DdlColumn.SMeta.ColumnComment) > 0 {
		comment := s.DdlColumn.SMeta.ColumnComment
		if strings.Contains(comment, "'") {
			comment = strings.ReplaceAll(comment, "'", "\\'")
		}
		if strings.Contains(comment, ";") {
			comment = strings.ReplaceAll(comment, ";", "\\;")
		}
		CommentValue = fmt.Sprintf("comment '%v'", comment)
	} else {
		CommentValue = fmt.Sprintf("comment ''")
	}
	alterSql = fmt.Sprintf("alter table `%s`.`%s` modify column `%s` %v %v %v %v %v %v;", s.TableInfo.Schema, s.TableInfo.Table, s.DdlColumn.Name, TypeConstraint, nullConstraint, ChartSetValue, CollationValue, DefaultValue, CommentValue)
	if _, ok := abGather["null"]; ok && len(abGather) == 1 {
		alterSql = ""
	}
	return global.AlterSqlResult{
		Record: record,
		Sql:    alterSql,
	}, nil
}
func (ms TableColumn) subPartSumMax(s []global.TablePartitionConfig) int {
	var sumMax int
	for _, v := range s {
		p := len(v.SubPartMeta)
		if p > sumMax {
			sumMax = p
		}
	}
	return sumMax
}
func (ms TableColumn) createTableSqlPartitionBody(s global.GetColumnTypeInput) (record []global.HeterogeneousConvertRecord, partitionColumn []string, partitionTableSql string) {
	var (
		partBodyColumn                            = make(map[string][]string)
		partColumnType                            = make(map[string]string)
		pm                                        []global.TablePartitionConfig
		partBodyColumnMap                         = make(map[string][]string)
		partBodyColumnHead, subpartBodyColumnHead string
		partitionRules                            []string
		subPartSumMax                             int
	)
	if _, ok := s.TableMeta.PartitionMeta["source"]; !ok || len(s.TableMeta.PartitionMeta) == 0 {
		return
	}
	pm = s.TableMeta.PartitionMeta["source"]
	if len(pm) == 0 {
		return
	}
	if len(pm[0].FirstPartition.ColumnName) > 0 {
		partBodyColumnMap["first"] = pm[0].FirstPartition.ColumnName
	}
	if len(pm[0].SubPartMeta) > 0 {
		partBodyColumnMap["sub"] = pm[0].SubPartMeta[0].ColumnName
	}
	//计算hash sub 分区最大值
	subPartSumMax = ms.subPartSumMax(s.TableMeta.PartitionMeta["source"])
	//处理主、子分区列及是否用到函数
	for _, w := range s.TableMeta.ColumnMeta["source"] {
		for k, v := range partBodyColumnMap {
			for _, p := range v {
				if strings.EqualFold(w.ColumnName, p) {
					partitionColumn = append(partitionColumn, w.ColumnName)
					partColumnType[p] = w.ConvertColumnType.Type
					switch {
					case strings.EqualFold(w.ConvertColumnType.Type, "decimal"):
						partBodyColumn[k] = append(partBodyColumn[k], fmt.Sprintf("floor(`%v`)", p))
						record = append(record, global.HeterogeneousConvertRecord{
							Object:   "partition column",
							Logo:     "func convert",
							OldValue: p,
							NewValue: fmt.Sprintf("floor(`%v`)", p),
							Reason:   "partition column data type decimal must is int",
						})
					case len(pm[0].FirstPartition.Func) > 0: //列使用到函数
						partBodyColumn[k] = append(partBodyColumn[k], fmt.Sprintf("%v(`%v`)", pm[0].FirstPartition.Func, p))
						record = append(record, global.HeterogeneousConvertRecord{
							Object:   "partition column",
							Logo:     "func convert",
							OldValue: fmt.Sprintf("%v(%v)", pm[0].FirstPartition.Func, p),
							NewValue: fmt.Sprintf("%v(`%v`)", pm[0].FirstPartition.Func, p),
							Reason:   "partition column data type func must Convert",
						})
					default:
						partBodyColumn[k] = append(partBodyColumn[k], fmt.Sprintf("`%v`", p))
					}
				}
			}
		}
	}
	//处理主分区列
	if columnX, ok := partBodyColumn["first"]; ok {
		if len(columnX) > 1 {
			partBodyColumnHead = fmt.Sprintf("columns (%v)", strings.Join(columnX, ","))
			record = append(record, global.HeterogeneousConvertRecord{
				Object:   "partition column",
				Logo:     "multiple column",
				OldValue: strings.Join(columnX, ","),
				NewValue: fmt.Sprintf("columns (%v)", strings.Join(columnX, ",")),
				Reason:   "partition multiple columns conversion",
			})
		} else if len(columnX) == 1 {
			for k, v := range partColumnType {
				if strings.Contains(columnX[0], k) {
					switch v {
					case "char", "varchar", "time", "timestamp":
						partBodyColumnHead = fmt.Sprintf("columns (%v)", columnX[0])
						record = append(record, global.HeterogeneousConvertRecord{
							Object:   "partition column",
							Logo:     "partition column type",
							OldValue: columnX[0],
							NewValue: fmt.Sprintf("columns (%v)", columnX[0]),
							Reason:   "partition column data type non-int conversion",
						})
					default:
						partBodyColumnHead = fmt.Sprintf("(%v)", columnX[0])
					}
				}
			}
		}
	}
	if columnX, ok := partBodyColumn["sub"]; ok {
		if len(columnX) > 1 {
			subpartBodyColumnHead = fmt.Sprintf("columns (%v)", strings.Join(columnX, ","))
		} else if len(columnX) == 1 {
			for k, v := range partColumnType {
				if strings.Contains(columnX[0], k) {
					switch v {
					//case "char", "varchar", "time", "timestamp":
					//	subpartBodyColumnHead = fmt.Sprintf("columns (%v)", columnX[0])
					default:
						subpartBodyColumnHead = fmt.Sprintf("(%v)", columnX[0])
					}
				}
			}
		}
		subpartBodyColumnHead = fmt.Sprintf("SUBPARTITION BY %v%v SUBPARTITIONS %v", s.TableMeta.PartitionMeta["source"][0].SubPartMeta[0].Meth, subpartBodyColumnHead, subPartSumMax)
	}
forLoop:
	for _, v := range pm {
		switch strings.ToUpper(v.FirstPartition.Meth) {
		case "RANGE":
			var subRulesGather []string
			pr := fmt.Sprintf("PARTITION %v VALUES LESS THAN (%v)", v.FirstPartition.Name, v.FirstPartition.Rules)
			switch v.SubPartMeth {
			case "HASH":
				for _, w := range v.SubPartMeta {
					subRulesGather = append(subRulesGather, fmt.Sprintf("SUBPARTITION `%v`", w.Name))
				}
				if len(v.SubPartMeta) < subPartSumMax {
					for k := 0; k < subPartSumMax-len(v.SubPartMeta); k++ {
						subRulesGather = append(subRulesGather, fmt.Sprintf("SUBPARTITION `add_sub_%v`", k))
						record = append(record, global.HeterogeneousConvertRecord{
							Object:   "sub partition",
							Logo:     "miss partition",
							OldValue: "",
							NewValue: fmt.Sprintf("SUBPARTITION `add_sub_%v`", k),
							Reason:   "sub partition sum is missing and is less than the defined quantity",
						})
					}
				}
			}
			if len(subRulesGather) > 0 {
				pr = fmt.Sprintf("%v(\n\t%v\n\t)", pr, strings.Join(subRulesGather, ",\n\t"))
			}
			partitionRules = append(partitionRules, pr)
		case "LIST":
			partitionRules = append(partitionRules, fmt.Sprintf("PARTITION %v VALUES IN (%v)", v.FirstPartition.Name, v.FirstPartition.Rules))
		case "HASH":
			partitionRules = append(partitionRules, fmt.Sprintf("PARTITIONS %v ", len(pm)))
			break forLoop
		case "KEY":
			partitionRules = append(partitionRules, fmt.Sprintf("PARTITION %v ", len(pm)))
			break forLoop
		}
	}
	if len(partitionRules) > 0 {
		partitionTableSql = fmt.Sprintf("PARTITION BY %v %v %v (\n %v \n)", pm[0].FirstPartition.Meth, partBodyColumnHead, subpartBodyColumnHead, strings.Join(partitionRules, ",\n"))
	}
	return
}

func (ms TableColumn) CreateIndex(s global.GetColumnTypeInput, partitionColumn []string, factor int) (recover []global.HeterogeneousConvertRecord, notNull []string, indexSql string) {
	var (
		creatText          []string
		indexMaxLength     = make(map[string]int)
		indexLength        = make(map[string][]int)
		newIndexColumn     = make(map[string][]string)
		newIndexColumnMeta = make(map[string][]global.TableMeta)
		indexNameType      = make(map[string] /*indexName + indexType*/ string)
		columnGather       = make(map[string] /*indexName + newIndexColumn */ []string)
	)
	if len(s.TableMeta.IndexMeta) == 0 {
		return
	}
	im, ok := s.TableMeta.IndexMeta["source"]
	if !ok {
		return
	}
	if len(im.IndexColumn) == 0 {
		return
	}
	//索引名及对应的索引类型集合
	for k := range im.IndexColumn {
		if t, ok1 := im.IndexType[k]; ok1 {
			indexNameType[k] = t
		}
	}
	for k, v := range im.IndexColumn {
		if t, ok1 := im.IndexType[k]; ok1 {
			var indexColumn1 []string
			for _, w := range v {
				indexColumn1 = append(indexColumn1, w.ColumnName)
			}
			switch t {
			case "uni", "pri":
				oldIndexColumn := indexColumn1
				add, _ := ea.CheckSum().Arrcmp(partitionColumn, indexColumn1)
				indexColumn1 = append(indexColumn1, add...)
				if len(add) > 0 {
					recover = append(recover, global.HeterogeneousConvertRecord{
						Object:   "index uniq",
						Logo:     k,
						OldValue: strings.Join(oldIndexColumn, ","),
						NewValue: strings.Join(indexColumn1, ","),
						Reason:   "primary or unique key must Include partition column",
					})
				}
			}
			newIndexColumn[k] = indexColumn1
		}
	}
	for k, v := range newIndexColumn {
		var newIndexLength int
		for _, w := range v {
			for _, l := range s.TableMeta.ColumnMeta["source"] {
				if strings.EqualFold(l.ColumnName, w) {
					_, p := ms.TypeConvert(l, factor)
					newIndexLength += p
					if y, z := newIndexColumnMeta[k]; z {
						newIndexColumnMeta[k] = append(y, l)
					} else {
						newIndexColumnMeta[k] = []global.TableMeta{l}
					}
					if y, z := indexLength[k]; z {
						indexLength[k] = append(y, p)
					} else {
						indexLength[k] = []int{p}
					}
				}
			}
		}
		indexMaxLength[k] = newIndexLength
	}

	for k, v := range indexMaxLength {
		avgLength := 3072 / len(indexLength[k])
		if v > 3072 {
			var newColumnGarth []string
			for n, m := range indexLength[k] {
				if m > avgLength {
					newColumnGarth = append(newColumnGarth, fmt.Sprintf("`%v`(%v)", newIndexColumn[k][n], avgLength/factor))
				} else {
					newColumnGarth = append(newColumnGarth, fmt.Sprintf("`%v`", newIndexColumn[k][n]))
				}
			}
			recover = append(recover, global.HeterogeneousConvertRecord{
				Object:   "index size",
				Logo:     k,
				OldValue: strings.Join(newIndexColumn[k], ","),
				NewValue: strings.Join(newColumnGarth, ","),
				Reason:   "index length >3072",
			})
			columnGather[k] = newColumnGarth
		} else {
			var newColumnGarth []string
			for n := range indexLength[k] {
				newColumnGarth = append(newColumnGarth, fmt.Sprintf("`%v`", newIndexColumn[k][n]))
			}
			columnGather[k] = newColumnGarth
		}
	}
	for k, v := range columnGather {
		switch indexNameType[k] {
		case "mul":
			creatText = append(creatText, fmt.Sprintf("KEY `%v` (%v)", k, strings.Join(v, ",")))
		case "uni":
			creatText = append(creatText, fmt.Sprintf("UNIQUE KEY `%v` (%v)", k, strings.Join(v, ",")))
		case "pri":
			notNull = newIndexColumn[k]
			creatText = append(creatText, fmt.Sprintf("PRIMARY KEY `%v` (%v)", k, strings.Join(v, ",")))
		}
	}
	indexSql = fmt.Sprintf("%v", strings.Join(creatText, ",\n"))
	return
}
func (ms TableColumn) createTableBody(s global.GetColumnTypeInput, notNull []string, tableIndexBody string, factor int) (recover []global.HeterogeneousConvertRecord, p string, err error) {
	var (
		creatText       []string
		createTableHead = fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%v`.`%v` (\n", s.TableInfo.Schema, s.TableInfo.Table)
		maxSize         int
		tableComment    = "''"
		cByte           int
		byteMerge       = make(map[int][]string)
		byteOrder       []int
	)
	if len(s.TableMeta.ColumnMeta) == 0 {
		return
	}
	//计算行长度
	for _, a := range s.TableMeta.ColumnMeta["source"] {
		_, cByte = ms.TypeConvert(a, factor)
		maxSize += cByte
		if b, ok := byteMerge[cByte]; ok {
			byteMerge[cByte] = append(b, a.ColumnName)
		} else {
			byteMerge[cByte] = []string{a.ColumnName}
			byteOrder = append(byteOrder, cByte)
		}
	}
	//倒序排列
	sort.Slice(byteOrder, func(i, j int) bool {
		return byteOrder[i] > byteOrder[j]
	})
	var varcharModifyText []string
	if maxSize > 65527 {
		tmpMaxSize := maxSize
	ll:
		for _, v := range byteOrder {
			for i := 0; i < len(byteMerge[v]); i++ {
				if tmpMaxSize < 65527 {
					break ll
				} else {
					varcharModifyText = append(varcharModifyText, byteMerge[v][i])
					tmpMaxSize = tmpMaxSize - v
				}
			}
		}
	}
	for _, a := range s.TableMeta.ColumnMeta["source"] {
		var (
			TypeConstraint string
			nullConstraint = fmt.Sprintf(" NULL ")
			ChartSetValue  string
			CollationValue string
			DefaultValue   string
			CommentValue   string
		)
		TypeConstraint, _ = ms.TypeConvert(a, factor)
		for _, b := range varcharModifyText {
			if strings.EqualFold(b, a.ColumnName) {
				switch a.ConvertColumnType.Type {
				case "varchar":
					TypeConstraint = "text"
					recover = append(recover, global.HeterogeneousConvertRecord{
						Object:   "table size",
						Logo:     b,
						OldValue: a.ColumnType,
						NewValue: TypeConstraint,
						Reason:   "table length >65535",
					})
				}
			}
		}
		if strings.EqualFold(a.IsNull, "NO") {
			nullConstraint = fmt.Sprintf(" NOT NULL ")
		} else {
			for _, z := range notNull {
				if strings.EqualFold(a.ColumnName, z) {
					nullConstraint = fmt.Sprintf(" NOT NULL ")
					recover = append(recover, global.HeterogeneousConvertRecord{
						Object:   "column null",
						Logo:     a.ColumnName,
						OldValue: "NULL",
						NewValue: "NOT NULL",
						Reason:   "primary key must is not null",
					})
				}
			}
		}
		if len(a.ColumnDefault) > 0 || strings.EqualFold(a.ColumnDefault, "''") {
			if !strings.EqualFold(a.ConvertColumnType.Type, "text") {
				DefaultValue = fmt.Sprintf("DEFAULT %v", ms.DefaultValueConvert(a.ConvertDefaultValue))
			} else {
				recover = append(recover, global.HeterogeneousConvertRecord{
					Object:   "column default",
					Logo:     a.ColumnName,
					OldValue: a.ConvertDefaultValue.OldValue,
					NewValue: "",
					Reason:   "text date Type not use default value",
				})
			}
		}
		if len(a.ColumnComment) > 0 {
			comment := a.ColumnComment
			if strings.Contains(comment, "'") {
				comment = strings.ReplaceAll(comment, "'", "\\'")
			}
			if strings.Contains(comment, ";") {
				comment = strings.ReplaceAll(comment, ";", "\\;")
			}
			CommentValue = fmt.Sprintf("comment '%v'", comment)
		} else {
			CommentValue = fmt.Sprintf("comment ''")
		}
		creatText = append(creatText, fmt.Sprintf("`%v` %v %v %v %v %v %v ", a.ColumnName, TypeConstraint, nullConstraint, DefaultValue, ChartSetValue, CollationValue, CommentValue))
	}
	if len(s.TableMeta.Comment["source"].Comment) > 0 {
		comment := s.TableMeta.Comment["source"].Comment
		if strings.Contains(comment, "'") {
			comment = strings.ReplaceAll(comment, "'", "\\'")
		}
		if strings.Contains(comment, ";") {
			comment = strings.ReplaceAll(comment, ";", "\\;")
		}
		tableComment = fmt.Sprintf("'%v'", comment)
	}
	p = fmt.Sprintf("%v %v \n) comment %v ", createTableHead, strings.Join(creatText, fmt.Sprintf(",\t\n")), tableComment)
	if len(tableIndexBody) > 0 {
		p = fmt.Sprintf("%v %v,\n %v \n) comment %v ", createTableHead, strings.Join(creatText, fmt.Sprintf(",\t\n")), tableIndexBody, tableComment)
	}
	return
}
func (ms TableColumn) CreateSql(s global.GetColumnTypeInput) (result global.CreatSqlResult, err error) {
	var (
		tableBody string
		factor    int
		//分区相关参数
		tablePartitionBody string
		partitionColumn    []string
		//索引相关的参数
		notNull         []string
		tableIndexBody  string
		record, record1 []global.HeterogeneousConvertRecord
	)
	record1, partitionColumn, tablePartitionBody = ms.createTableSqlPartitionBody(s)
	record = append(record, record1...)
	record1, notNull, tableIndexBody = ms.CreateIndex(s, partitionColumn, factor)
	record = append(record, record1...)
	record1, tableBody, err = ms.createTableBody(s, notNull, tableIndexBody, factor)
	record = append(record, record1...)
	/*
		遇到的问题
		1: 索引长度超限（767），必要时可使用前缀索引或减少数据类型长度   已解决
		2：行长度超限（65535），超过优先转varchar最大的，逐层依次降低  已解决
		3：partition column 不能有comment，create table不受限制 alter comment 受限制   已解决
		4：partition column type 是decimal，需要转换为flot    已解决
		5：partition column type 是varchar,datetime，需要增加partition by range columns  已解决
		6：表存在主键索引或唯一索引时，需要包含分区列     已解决
		7：range 或list 分区 values less than ('') 存在单引号且数据类型为int类型，转换失败  已解决
		8：mysql体系不允许大字段有blobText，json，longtext 有默认值问  已解决
		9:  comment 中包含单引号或者;号问题     已解决
	*/
	return global.CreatSqlResult{
		Record: record,
		Sql:    fmt.Sprintf("%v %v;", tableBody, tablePartitionBody),
	}, nil
}
